/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.Order;
import me.zhengjie.business.domain.vo.OrderCntVo;
import me.zhengjie.business.domain.vo.OrderVo;
import me.zhengjie.business.domain.vo.OrderWxVo;
import me.zhengjie.business.domain.vo.ZhgkxmVo;
import me.zhengjie.pay.domain.QueryRefundVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-08-03
**/
public interface OrderRepository extends JpaRepository<Order, String>, JpaSpecificationExecutor<Order> {

    Page<Order> queryByBisOrderZhidInAndBisOrderZt(List<Long> bisOrderZhids, String bisOrderZt, Pageable pageable);

    @Query(
            value = "select t1.bis_zhsq_grbh, t4.input " +
                    "from bis_zhsq t1 , bis_jzdj t2, bis_order t3, mnt_ybc t4 " +
                    "where t1.bis_zhsq_id=t2.bis_jzdj_zhsq_id " +
                    "and t2.bis_jzdj_order_id=t3.bis_order_id " +
                    "and t2.bis_jzdj_id=t4.ywid " +
                    "and t4.ywbm='2210' " +
                    "and t3.bis_order_id=?1 " +
                    "and t4.fwlx=?2 ",
            nativeQuery = true)
    Object query2210(String orderId, String fwlx);



    /**
     * 根据月份，查询每月的订单数量
     * @param month /
     * @return /
     */
    @Query(value = "select count(t.bis_order_id) content, t.bis_order_hlrq hlrq from BIS_ORDER t " +
            "where t.bis_order_hlrq like ?1||'%' " +
            "group by t.bis_order_hlrq",nativeQuery = true)
    List<Object> groupByHlry2Count(String month);

    @Query(value = "select r2.bis_gsgl_qymc qymc, count(1) cnt " +
            "  from bis_order r0, bis_zhsq r1, bis_gsgl r2 " +
            " where r0.bis_order_zhid = r1.bis_zhsq_id " +
            "   and r1.bis_zhsq_hljg_id = r2.bis_gsgl_id " +
            "   and r0.bis_order_hlrq = ?1 " +
            " group by r2.bis_gsgl_qymc",
            countQuery = "SELECT  COUNT(1)  FROM  (" +
            "  select r2.bis_gsgl_qymc qymc, count(1) cnt " +
            "  from bis_order r0, bis_zhsq r1, bis_gsgl r2 " +
            " where r0.bis_order_zhid = r1.bis_zhsq_id " +
            "   and r1.bis_zhsq_hljg_id = r2.bis_gsgl_id " +
            "   and r0.bis_order_hlrq = ?1 " +
            " group by r2.bis_gsgl_qymc )",
            nativeQuery = true)
    Page<Object> queryOrderCnt(String hlrq, Pageable pageable);


    /**
     * 更改订单为过期订单
     */
    @Modifying
    @Query(value = "update bis_order set bis_order_zt='20' where bis_order_id in " +
            "(select o.bis_order_id from bis_order o where o.bis_order_hlrq=to_char(sysdate-1, 'yyyy-mm-dd') and o.bis_order_zt='0')",nativeQuery = true)
    void updateGqdd();

    @Query(value = "select count(*) from BIS_VERIFY_PERSON t where t.order_id=?1 ", nativeQuery = true)
    Integer queryVerifyPerson(String orderId);


    @Query(value = "select to_char(BIS_ORDER_HLSTART,'yyyy-mm-dd hh24:mi:ss') from bis_order t where t.bis_order_id=?1 ", nativeQuery = true)
    Object queryOrderHlstartById(String orderId);



    /**
     * 查询居家订单，用于分页
     * @param jgxx
     * @param ryxx
     * @param ddzt
     * @param pageable
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.OrderVo( " +
            "r1.bisZhsqId, " +
            "r1.bisZhsqSnryXm, " +
            "r1.bisZhsqSnrySfz, " +
            "r1.bisZhsqDykkrq, " +
            "r1.bisZhsqDyjsrq, " +
            "r1.bisZhsqZt, " +
            "r2.bisOrderId, " +
            "r2.bisOrderHlrq, " +
            "r2.bisOrderStartTime, " +
            "r2.bisOrderEndTime, " +
            "r2.bisOrderHlyName, " +
            "r2.bisOrderZt, " +
            "r2.bisOrderHlstart, " +
            "r2.bisOrderHlend, " +
            "r4.aae036, " +
            "r4.akc264, " +
            "r4.akc261, " +
            "r4.akc260, " +
            "r1.bisZhsqLxdh," +
            "r1.bisZhsqJhrxm," +
            "r1.bisZhsqJhrdh," +
            "r1.bisZhsqHldz," +
            "r1.bisZhsqHldzXxdz," +
            "r6.bisGsglYljgdm, " +
            "r6.bisGsglQymc) " +
            "from Zhsq r1 " +
            "inner join Order r2 on r1.bisZhsqId = r2.bisOrderZhid " +
            "left join Jzdj r3 on r2.bisOrderId = r3.bisJzdjOrderId " +
            "left join KC24 r4 on r3.bisJzdjLsh = r4.akc190 " +
            "inner join Qy r5 on r1.bisZhsqId = r5.bisQyZhsqId " +
            "inner join Hljg r6 on r5.bisQyHljgId = r6.bisGsglId " +
            " where  1=1 " +
            "   and r5.bisQyZt = 'qygl03' " +
            "   and (?1 IS NULL OR ?1 = '' OR r6.bisGsglYljgdm = ?1 OR r6.bisGsglQymc like '%'||?1||'%') " +
            "   and (?2 IS NULL OR ?2 = '' OR r1.bisZhsqSnryXm = ?2 OR r1.bisZhsqSnrySfz like '%'||?2||'%' ) " +
            "   and (?3 IS NULL OR ?3 = '' OR r2.bisOrderZt = ?3 ) " +
            "   and (?4 IS NULL OR ?4 = '' OR r2.bisOrderHlrq = ?4 ) " +
            "   and (?5 IS NULL OR ?5 = '' OR r6.sbgs.bisGsglId = ?5 ) ",
            countQuery = "select count(*)  " +
                    "from Zhsq r1 " +
                    "inner join Order r2 on r1.bisZhsqId = r2.bisOrderZhid " +
                    "left join Jzdj r3 on r2.bisOrderId = r3.bisJzdjOrderId " +
                    "left join KC24 r4 on r3.bisJzdjLsh = r4.akc190 " +
                    "inner join Qy r5 on r1.bisZhsqId = r5.bisQyZhsqId " +
                    "inner join Hljg r6 on r5.bisQyHljgId = r6.bisGsglId " +
                    " where 1=1 " +
                    "   and r5.bisQyZt = 'qygl03' " +
                    "   and (?1 IS NULL OR ?1 = '' OR r6.bisGsglYljgdm = ?1 OR r6.bisGsglQymc like '%'||?1||'%') " +
                    "   and (?2 IS NULL OR ?2 = '' OR r1.bisZhsqSnryXm = ?2 OR r1.bisZhsqSnrySfz like '%'||?2||'%' ) " +
                    "   and (?3 IS NULL OR ?3 = '' OR r2.bisOrderZt = ?3 ) " +
                    "   and (?4 IS NULL OR ?4 = '' OR r2.bisOrderHlrq = ?4 ) " +
                    "   and (?5 IS NULL OR ?5 = '' OR r6.sbgs.bisGsglId = ?5 ) ")
    Page<OrderVo> queryAllOrderList(String jgxx, String ryxx, String ddzt, String hlrq, String sbgs, Pageable pageable);


    /**
     * 查询居家订单，用于导出
     * @param jgxx
     * @param ryxx
     * @param ddzt
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.OrderVo( " +
            "r1.bisZhsqId, " +
            "r1.bisZhsqSnryXm, " +
            "r1.bisZhsqSnrySfz, " +
            "r1.bisZhsqDykkrq, " +
            "r1.bisZhsqDyjsrq, " +
            "r1.bisZhsqZt, " +
            "r2.bisOrderId, " +
            "r2.bisOrderHlrq, " +
            "r2.bisOrderStartTime, " +
            "r2.bisOrderEndTime, " +
            "r2.bisOrderHlyName, " +
            "r2.bisOrderZt, " +
            "r2.bisOrderHlstart, " +
            "r2.bisOrderHlend, " +
            "r4.aae036, " +
            "r4.akc264, " +
            "r4.akc261, " +
            "r4.akc260, " +
            "r1.bisZhsqLxdh," +
            "r1.bisZhsqJhrxm," +
            "r1.bisZhsqJhrdh," +
            "r1.bisZhsqHldz," +
            "r1.bisZhsqHldzXxdz," +
            "r6.bisGsglYljgdm, " +
            "r6.bisGsglQymc) " +
            "from Zhsq r1 " +
            "inner join Order r2 on r1.bisZhsqId = r2.bisOrderZhid " +
            "left join Jzdj r3 on r2.bisOrderId = r3.bisJzdjOrderId " +
            "left join KC24 r4 on r3.bisJzdjLsh = r4.akc190 " +
            "inner join Qy r5 on r1.bisZhsqId = r5.bisQyZhsqId " +
            "inner join Hljg r6 on r5.bisQyHljgId = r6.bisGsglId " +
            " where  1=1 " +
            "   and r5.bisQyZt = 'qygl03' " +
            "   and (?1 IS NULL OR ?1 = '' OR r6.bisGsglYljgdm = ?1 OR r6.bisGsglQymc like '%'||?1||'%') " +
            "   and (?2 IS NULL OR ?2 = '' OR r1.bisZhsqSnryXm = ?2 OR r1.bisZhsqSnrySfz like '%'||?2||'%' ) " +
            "   and (?3 IS NULL OR ?3 = '' OR r2.bisOrderZt = ?3 ) " +
            "   and (?4 IS NULL OR ?4 = '' OR r2.bisOrderHlrq = ?4 ) " +
            "   and (?5 IS NULL OR ?5 = '' OR r6.sbgs.bisGsglId = ?5 ) ")
    List<OrderVo> queryAllOrderList(String jgxx, String ryxx, String ddzt, String hlrq, String sbgs);



    /**
     * 查询居家订单，用于分页
     * @param pageable
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.OrderWxVo( " +
            "r1.bisOrderId, " +
            "r1.bisOrderZhid, " +
            "r1.bisOrderFaid, " +
            "r1.bisOrderHlrq, " +
            "r1.bisOrderHlyId, " +
            "r1.bisOrderHlyName, " +
            "r1.bisOrderServiceType, " +
            "r1.bisOrderStartTime, " +
            "r1.bisOrderEndTime, " +
            "r1.bisOrderJd, " +
            "r1.bisOrderWd, " +
            "r1.bisOrderZt, " +
            "r1.bisOrderHlstart, " +
            "r1.bisOrderHlend, " +
            "r1.bisOrderHlsc, " +
            "r1.bisOrderPjzt, " +
            "r1.bisOrderXjzt, " +
            "r2) " +
            "from Order r1 " +
            "inner join Zhsq r2 on r2.bisZhsqId = r1.bisOrderZhid " +
            " where  1=1 " +
            "   and r1.bisOrderZt in (?1) " +
            "   and r1.bisOrderHlyId = ?2 " +
            "   and (?3 IS NULL OR ?3 = '' OR r2.bisZhsqSnryXm like '%'||?3||'%' ) ",
            countQuery = "select count(*)  " +
                    "from Order r1 " +
                    "inner join Zhsq r2 on r2.bisZhsqId = r1.bisOrderZhid " +
                    " where 1=1 " +
                    "   and r1.bisOrderZt in (?1) " +
                    "   and r1.bisOrderHlyId = ?2 " +
                    "   and (?3 IS NULL OR ?3 = '' OR r2.bisZhsqSnryXm like '%'||?3||'%' ) ")
    Page<OrderWxVo> queryAllOrderWxVoList(List<String> zt, Long hlyid, String xm, Pageable pageable);
}